package com.shuhe.handler;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.shuhe.JettyServer;
import com.shuhe.common.CommonFunction;
import com.shuhe.common.HttpClient;
import com.shuhe.common.ReflectUtil;
import com.shuhe.domain.api.ApiAlgorithmRequest;
import com.shuhe.domain.mxgc.*;
import com.shuhe.domain.token.Token;
import com.shuhe.metastore.AISql;
import com.shuhe.metastore.DBJrSql;
import com.shuhe.metastore.DBSql;
import com.shuhe.metastore.MetaMysql;
import org.apache.log4j.Logger;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;

import static com.shuhe.JettyServer.algorithmUrl;
import static com.shuhe.JettyServer.globalProperties;
import static com.shuhe.common.CommonFunction.*;
import static com.shuhe.common.MyDateUtils.getDataString;
import static com.shuhe.common.MyDateUtils.getNowDate;
import static com.shuhe.handler.CommonHandler.reqExpire;
import static com.shuhe.handler.ServiceDetail.*;


public class MetaHandler extends HttpServlet {

    private static Logger logger = Logger.getLogger(MetaHandler.class);

    @Override
    protected void doOptions(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp = setResponse(resp);
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        try {
            resp = setResponse(resp);

            String tokenKey = req.getHeader("token");
            Token token = JettyServer.getGlobalToken(tokenKey);
            String bodyStr = readAsChars(req);
            JSONObject bodyJson = JSONObject.parseObject(bodyStr);
            SqlRequestBody sqlReq = JSONObject.toJavaObject(bodyJson, SqlRequestBody.class);

            //操作日志
            reqExpire(req, bodyJson);

            logger.info(sqlReq.getOperateType());

            //根据数据源，得到表列表
            if (sqlReq.getOperateType().equals("dataSource_getTable")) {
                String sqlResp = dataSource_getTable(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }

            //根据数据源，得到字段列表
            if (sqlReq.getOperateType().equals("dataSource_getField")) {
                String sqlResp = dataSource_getField(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }

            //根据数据源，显示所选表数据
            if (sqlReq.getOperateType().equals("showDataByMeta")) {
                String sqlResp = showDataByMeta(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }

            //根据数据源，采集元数据
            if (sqlReq.getOperateType().equals("dataSource_make")) {
                String sqlResp = dataSource_make(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }

            //建立标准表
            if (sqlReq.getOperateType().equals("standard_create")) {
                String sqlResp = standard_create(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            logger.info(ex.getMessage());
            resp.getWriter().write(returnFail(ex.getMessage()));
        }

    }

    public static DatabaseMeta getDatabaseMeta(Connection metaConn, String id) throws Exception {
        String sql = "select id, datasource_name, db_type, ip, source_port, db_name, db_user_name, password" + " " +
                "from mxgc_user_sjygl where id = '" + id + "'";
        ResultSet rs = MetaMysql.querySql(metaConn, sql);
        rs.next();
        DatabaseMeta meta = getDbName(rs);
        return meta;
    }


    public static String dataSource_getTable(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String select_sql = "select a.id, a.table_name, a.user_name, case when b.is_share is null then '' else b.is_share end as is_share\n" +
                    "from mxgc_user_task_sjjr a left join standard_table b\n" +
                    "on a.desttable_name = concat('original.', b.table_name)\n" +
                    "where a.desttable_name like 'original.%'";
            ResultSet rs_select = MetaMysql.querySql(metaConn, select_sql);
            ArrayList<String> select_tables = new ArrayList<>();
            ArrayList<String> tasks = new ArrayList<>();
            ArrayList<String> users = new ArrayList<>();
            ArrayList<String> shares = new ArrayList<>();
            while (rs_select.next()) {
                select_tables.add(rs_select.getString("table_name"));
                tasks.add(rs_select.getString("id"));
                users.add(rs_select.getString("user_name"));
                shares.add(rs_select.getString("is_share"));
            }
            String id = bodyJson.getString("id");
            DatabaseMeta meta = getDatabaseMeta(metaConn, id);
            dbConn = meta.getDbConn();
            String sql = meta.getTableSql();
            String db_name = meta.getDbName();
            if (db_name != null && db_name.length() > 0) {
                ArrayList<Object> datas = new ArrayList();
                // 获取所有表的信息
                PreparedStatement pstmt = dbConn.prepareStatement(sql);
                pstmt.setString(1, db_name);
                ResultSet rs1 = pstmt.executeQuery();
                DynamicBean dynamicBean = new DynamicBean();
                while (rs1.next()) {
                    Map<String, Object> addProperties = new HashMap();
                    String table_comment = rs1.getString("table_comment");
                    String table_name = rs1.getString("table_name");
                    if (table_comment == null) {
                        table_comment = table_name;
                    }
                    addProperties.put("name", table_comment);
                    addProperties.put("key", table_name);

                    String full_name = db_name + "." + table_name;
                    int pos = select_tables.indexOf(full_name);
                    if (pos >= 0) {
                        addProperties.put("is_exist", "y");
                        addProperties.put("task_id", tasks.get(pos));
                        addProperties.put("user_name", users.get(pos));
                        addProperties.put("is_share", shares.get(pos));
                    } else {
                        addProperties.put("is_exist", "n");
                        addProperties.put("task_id", "");
                        addProperties.put("user_name", "");
                        addProperties.put("is_share", "");
                    }

                    Object obj = ReflectUtil.getTarget(dynamicBean, addProperties);
                    datas.add(obj);
                }
                SqlResponseResult result = new SqlResponseResult();
                result.setDatas(datas);
                return setSuccessBodyByResult(result);
            }
            SqlResponseResult result = new SqlResponseResult();
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }

    public static DatabaseMeta getDbName(ResultSet rs) throws Exception {
        DatabaseMeta meta = new DatabaseMeta();
        if (rs.getString("db_type").equals("mysql")) {
            String ip = rs.getString("ip");
            String port = rs.getString("source_port");
            String db_name = rs.getString("db_name");
            String url = "jdbc:mysql://" + ip + ":" + port + "/" + db_name + "?serverTimezone=Hongkong" +
                    "&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect" +
                    "=true&useSSL=false";
            Connection dbConn = DBSql.connectByAll(url, rs.getString("db_user_name"), rs.getString("password"), "com" +
                    ".mysql" +
                    ".jdbc.Driver");
            String tableSql = "SELECT table_name, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?";
            String fieldSql = "select column_name, column_comment, ordinal_position, data_type,  " +
                    "    CASE \n" +
                    "        WHEN column_type LIKE '%(%)%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(column_type, " +
                    "'(', -1), ')', 1)\n" +
                    "        ELSE '' END AS extracted_value " +
                    "from INFORMATION_SCHEMA.columns where table_schema=? and table_name = ?" +
                    " order by ordinal_position";
            meta.setDbName(db_name);
            meta.setTableSql(tableSql);
            meta.setFieldSql(fieldSql);
            meta.setDbConn(dbConn);
            meta.setDbType("mysql");
        }
        if (rs.getString("db_type").equals("postgresql")) {
            String ip = rs.getString("ip");
            String port = rs.getString("source_port");
            String db_name = rs.getString("db_name");
            String url = "jdbc:postgresql://" + ip + ":" + port + "/" + db_name;
            Connection dbConn = DBSql.connectByAll(url, rs.getString("db_user_name"), rs.getString("password"), "com" +
                    ".mysql" +
                    ".jdbc.Driver");
            if (db_name.contains("=")) {
                db_name = db_name.split("=")[1];
            } else {
                db_name = "";
            }
            String tableSql = "select * from (" +
                    "SELECT \n" +
                    "    c.relname AS table_name,\n" +
                    "    obj_description(c.oid) AS table_comment\n" +
                    "FROM \n" +
                    "    pg_class c\n" +
                    "JOIN \n" +
                    "    pg_namespace n ON n.oid = c.relnamespace\n" +
                    "WHERE \n" +
                    "    c.relkind = 'r'\n" +
                    "    AND n.nspname = ?\n) d where 1=1";
            String fieldSql = "SELECT\n" +
                    "    c.column_name AS column_name,\n" +
                    "    pgd.description AS column_comment,\n" +
                    "    case \n" +
                    "\t\twhen c.data_type = 'character varying' then 'varchar' else c.data_type end AS data_type,\n" +
                    "    CASE\n" +
                    "        WHEN c.data_type = 'character varying' THEN concat(c.character_maximum_length, '')\n" +
                    "        WHEN c.data_type = 'numeric' THEN concat(c.numeric_precision, ',', c.numeric_scale)\n" +
                    "        ELSE ''\n" +
                    "    END AS extracted_value,\n" +
                    "    c.ordinal_position AS ordinal_position\n" +
                    "FROM\n" +
                    "    information_schema.columns c\n" +
                    "LEFT JOIN\n" +
                    "    pg_class pgc ON pgc.relname = c.table_name\n" +
                    " AND pgc.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = c.table_schema) " +
                    "LEFT JOIN\n" +
                    "    pg_namespace pgn ON pgn.oid = pgc.relnamespace AND pgn.nspname = c.table_schema\n" +
                    "LEFT JOIN\n" +
                    "    pg_description pgd ON pgd.objoid = pgc.oid AND pgd.objsubid = c.ordinal_position\n" +
                    "WHERE\n" +
                    "    c.table_schema = ?\n" +
                    "    AND c.table_name = ?\n" +
                    "ORDER BY\n" +
                    "    c.ordinal_position";
            meta.setDbName(db_name);
            meta.setTableSql(tableSql);
            meta.setFieldSql(fieldSql);
            meta.setDbConn(dbConn);
            meta.setDbType("postgresql");
        }
        return meta;
    }

    public static String showDataByMeta(JSONObject bodyJson) throws Exception {
        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            String id = bodyJson.getString("id");
            String sql = bodyJson.getString("sql");
            metaConn = MetaMysql.connect();
            DatabaseMeta meta = getDatabaseMeta(metaConn, id);
            dbConn = meta.getDbConn();

            String countSql = "select count(1) from( " + sql + ") sql_count";
            ResultSet rs = DBSql.querySql(dbConn, countSql);
            rs.next();
            String sqlCount = rs.getString(1);
            //查询数据
            if (meta.getDbType().equals("oracle")) {
                sql = "select * from (" + sql + ") where rownum <= 100";
            } else {
                if (meta.getDbType().equals("sqlServer")) {
                    sql = "select top 100 * from (" + sql + ") a";
                } else {
                    sql = sql + " limit 100";
                }
            }
            rs = DBSql.querySql(dbConn, sql);

            SqlResponseBody resp = new SqlResponseBody();
            resp.setCommon(getCommonResponseBody(beginTime));
            SqlRequestBody sqlReq = new SqlRequestBody();
            String fieldStr = dataSource_getField(bodyJson);
            JSONArray fieldMetas = JSONObject.parseObject(fieldStr).getJSONObject("result").getJSONArray("datas");
            ArrayList<SqlResponseResultMeta> metas = new ArrayList();
            for (int i = 0; i < fieldMetas.size(); i++) {
                JSONObject fieldMeta = fieldMetas.getJSONObject(i);
                SqlResponseResultMeta field = new SqlResponseResultMeta();
                field.setField(fieldMeta.getString("column_name"));
                String comment = fieldMeta.getString("column_comment");
                if (comment == null || comment.length() == 0){
                    comment = fieldMeta.getString("column_name");
                }
                field.setName(comment);
                metas.add(field);
            }
            sqlReq.setMetas(metas);
            String returnStr = getSqlResponseResult(sqlReq, rs, resp, metaConn, sqlCount, "100");
            return returnStr;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }

    public static String dataSource_getField(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            String id = bodyJson.getString("id");
            metaConn = MetaMysql.connect();
            String table_name = bodyJson.getString("table_name");
            DatabaseMeta meta = getDatabaseMeta(metaConn, id);
            dbConn = meta.getDbConn();
            String sql = meta.getFieldSql();
            String db_name = meta.getDbName();
            if (db_name.length() > 0) {
                ArrayList<Object> datas = new ArrayList();
                PreparedStatement pstmt = dbConn.prepareStatement(sql);
                pstmt.setString(1, db_name);
                pstmt.setString(2, table_name);
                ResultSet rs1 = pstmt.executeQuery();
                DynamicBean dynamicBean = new DynamicBean();
                while (rs1.next()) {
                    Map<String, Object> addProperties = new HashMap();
                    String column_name = rs1.getString("column_name");
                    String column_comment = rs1.getString("column_comment");
                    if (column_comment == null) {
                        column_comment = column_name;
                    }
                    String ordinal_position = rs1.getString("ordinal_position");
                    String column_type = rs1.getString("data_type");
                    String column_size = rs1.getString("extracted_value");
                    addProperties.put("column_name", column_name);
                    addProperties.put("column_comment", column_comment);
                    addProperties.put("ordinal_position", ordinal_position);
                    addProperties.put("column_type", column_type);
                    addProperties.put("column_size", column_size);


                    Object obj = ReflectUtil.getTarget(dynamicBean, addProperties);
                    datas.add(obj);
                }
                SqlResponseResult result = new SqlResponseResult();
                result.setDatas(datas);
                return setSuccessBodyByResult(result);
            }
            SqlResponseResult result = new SqlResponseResult();
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }

    public static String dataSource_make(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            String id = bodyJson.getString("id");
            String tables = bodyJson.getString("tables");
            metaConn = MetaMysql.connect();
            DatabaseMeta meta = getDatabaseMeta(metaConn, id);
            dbConn = meta.getDbConn();
            String sql = meta.getTableSql();
            String db_name = meta.getDbName();
            if (db_name.length() > 0) {
                PreparedStatement pstmt = dbConn.prepareStatement(sql);
                pstmt.setString(1, db_name);
                if (tables != null && !tables.isEmpty()) {
                    String[] tableArray = tables.split(",");
                    StringBuilder inClause = new StringBuilder();

                    for (int i = 0; i < tableArray.length; i++) {
                        if (i > 0) {
                            inClause.append(",");
                        }
                        inClause.append("?"); // 占位符
                    }

                    sql += " AND table_name IN (" + inClause.toString() + ")";

                    // 创建预编译语句
                    pstmt = dbConn.prepareStatement(sql);

                    // 设置参数
                    pstmt.setString(1, db_name); // 第一个参数是 db_name

                    // 设置 IN 子句中的多个表名
                    for (int i = 0; i < tableArray.length; i++) {
                        pstmt.setString(i + 2, tableArray[i].trim()); // 参数从第2个开始
                    }
                }


                ResultSet rs1 = pstmt.executeQuery();
                while (rs1.next()) {
                    String table_comment = rs1.getString("table_comment");
                    String table_name = rs1.getString("table_name");
                    // 按table_name, datasource_id,  从meta_table删除相关数据
                    String delSql = "delete from meta_table where table_name = '" + table_name + "' and datasource_id" +
                            " = '" + id + "'";
                    MetaMysql.execSql(metaConn, delSql);
                    sql = meta.getFieldSql();
                    if (sql.length() > 0) {
                        PreparedStatement pstmt2 = dbConn.prepareStatement(sql);
                        pstmt2.setString(1, db_name);
                        pstmt2.setString(2, table_name);
                        ResultSet rs2 = pstmt2.executeQuery();
                        while (rs2.next()) {
                            //生成meta_table的插入语句
                            String insertSql = "insert into meta_table(id, datasource_id, table_name, table_comment, " +
                                    "column_name, column_comment, column_type, column_size, column_order, update_time) " +
                                    "values('" +
                                    CommonFunction.getUUID() + "', '" + id + "', '" + table_name + "', '" + table_comment
                                    + "', '" + rs2.getString("column_name") + "', '" +
                                    rs2.getString("column_comment") + "', '" +
                                    rs2.getString("data_type") + "', '" +
                                    rs2.getString("extracted_value") + "', '" +
                                    rs2.getString("ordinal_position") + "', '" + getNowDate() + "')";
                            MetaMysql.execSql(metaConn, insertSql);
                        }
                    }
                }
                SqlResponseResult result = new SqlResponseResult();
                return setSuccessBodyByResult(result);
            }
            SqlResponseResult result = new SqlResponseResult();
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }

    public static String standard_create(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            ArrayList<String> sqls = new ArrayList();
            String table_id = bodyJson.getString("id");
            String user_name = bodyJson.getString("userName");
            String isNew = bodyJson.getString("isNew");
            String bz1 = bodyJson.getString("bz1");
            if (bz1 == null){
                bz1 = "";
            }
            String mlid = bodyJson.getString("mlid");
            JSONObject tableJson = bodyJson.getJSONObject("table");
            String table_name = tableJson.getString("table_name");
            String table_comment = tableJson.getString("table_comment");
            String table_type = tableJson.getString("table_type");
            String isCreate = bodyJson.getString("isCreate");
            if (isCreate == null) {
                isCreate = "y";
            }
            JSONArray addColumns = bodyJson.getJSONArray("addColumns");
            if (addColumns == null) {
                addColumns = new JSONArray();
            }
            String update_time = getNowDate();
            String sql = "select id from standard_table where table_name = '" + table_name + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                if (isNew.equals("y")) {
                    return returnFail("表名重复");
                }
            }

            sql = "delete from standard_table where id = '" + table_id + "'";
            sqls.add(sql);
            sql = "delete from standard_column where table_id = '" + table_id + "'";
            sqls.add(sql);
            sql = "insert into standard_table(id, user_name, mlid, table_name, table_comment, update_time, " +
                    "table_type, bz1) values" +
                    "('" + table_id + "', '" + user_name + "', '" + mlid + "', '" + table_name + "', '" + table_comment + "', '"
                    + update_time + "', '" + table_type + "', '" + bz1 + "')";
            sqls.add(sql);
            JSONArray columns = tableJson.getJSONArray("extraColumns");


            // 将columns 的数据，存储到 standard_column
            for (int i = 0; i < columns.size(); i++) {
                JSONObject column = columns.getJSONObject(i);
                String column_id = CommonFunction.getUUID();
                String column_name = column.getString("column_name");
                String column_comment = column.getString("column_comment");
                String column_type = column.getString("column_type");
                String column_size = column.getString("column_size");
                String column_order = column.getString("column_order");
                sql = "insert into standard_column(id, table_id, column_name, column_comment, column_type, " +
                        "column_size, column_order) " +
                        "values('" + column_id + "', '" + table_id + "', '" + column_name + "', '" + column_comment + "', '" +
                        column_type + "', '" + column_size + "', '" + column_order + "')";
                sqls.add(sql);
            }

            for (String query : sqls) {
                //元数据存储
                MetaMysql.execSql(metaConn, query);
            }

            if (table_type.equals("original")) {
                table_name = "original." + table_name;
                if (globalProperties.getProperty("db.type").equals("gbase")) {
                    dbConn = DBJrSql.connect();
                } else{
                    dbConn = DBSql.connect();
                }

            }
            if (table_type.equals("self_owned")) {
                table_name = "self_owned." + table_name;
                dbConn = DBSql.connect();
            }
            if (isCreate.equals("y")) {
                //开始建表
                sql = generateCreateTableStatement(table_name, columns);
                DBSql.execSql(dbConn, "drop table if exists " + table_name);
                DBSql.execSql(dbConn, sql);
                addColumns = new JSONArray();
            }
            if (addColumns.size() > 0) {
                for (int i = 0; i < addColumns.size(); i++) {
                    JSONObject column = addColumns.getJSONObject(i);
                    String column_name = column.getString("column_name");
                    String column_type = column.getString("column_type");
                    String column_size = column.getString("column_size");
                    if (column_type.startsWith("date")) {
                        column_size = "";
                    }
                    sql = "alter table " + table_name + " add column " + column_name + " " + column_type + "(" + column_size + ")";
                    DBSql.execSql(dbConn, sql);
                }
            }

            SqlResponseResult result = new SqlResponseResult();
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }


    /**
     * 根据 table_name 和 columns 生成建表语句
     *
     * @param table_name 表名
     * @param columns    列信息数组
     * @return 建表语句
     */
    public static String generateCreateTableStatement(String table_name, JSONArray columns) {
        StringBuilder createTableSql = new StringBuilder();
        createTableSql.append("CREATE TABLE ").append(table_name).append(" (");

        for (int i = 0; i < columns.size(); i++) {
            JSONObject column = columns.getJSONObject(i);
            String column_name = column.getString("column_name");
            String column_type = column.getString("column_type");
            String column_size = column.getString("column_size");

            // 构建列定义
            createTableSql.append(column_name).append(" ").append(column_type);
            if (column_type.startsWith("date") || column_type.indexOf("text") >= 0) {
                column_size = "";
            }
            if (column_size != null && !column_size.isEmpty()) {
                createTableSql.append("(").append(column_size).append(")");
            }

            // 如果不是最后一列，添加逗号
            if (i < columns.size() - 1) {
                createTableSql.append(", ");
            }
        }

        createTableSql.append(");");
        return createTableSql.toString();
    }

}
